
'--------------------------------------------------
' Hands-On 19-1
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-2
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-3
' No code in this Hands-On.
' Please follow the instructions in the book.
'
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-4
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-5
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-6
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-7
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-8
'--------------------------------------------------

Sub DoSurvey()
    InfoSurvey.Show
End Sub


'--------------------------------------------------
' Hands-On 19-9
'--------------------------------------------------

Private Sub UserForm_Initialize()
'select the Hardware option
    optHard.Value = True
'turn off the Software option and all the check boxes
    optSoft.Value = False
    chkIBM.Value = False
    chkNote.Value = False
    chkMac.Value = False
    'display a zero in the text box
    txtPercent.Value = 0
' call ListHardware procedure
    Call ListHardware
'populate the combo box
    With Me.cboxWhereUsed
        .AddItem "Home"
        .AddItem "Work"
        .AddItem "School"
        .AddItem "Work/home"
        .AddItem "Home/school"
        .AddItem "Work/home/school"
    End With
 'select the first element in the list box
    Me.cboxWhereUsed.ListIndex = 0
'load a picture file for the Hardware option
    Me.picImage.Picture = LoadPicture("c:\Ex07_HandsOn\cd.bmp")
End Sub


Sub ListHardware()
    With InfoSurvey.lboxSystems
        .AddItem "CD-ROM Drive"
        .AddItem "Printer"
        .AddItem "Fax"
        .AddItem "Network"
        .AddItem "Joystick"
        .AddItem "Sound Card"
        .AddItem "Graphics Card"
        .AddItem "Modem"
        .AddItem "Monitor"
        .AddItem "Mouse"
        .AddItem "Zip Drive"
        .AddItem "Scanner"
    End With
End Sub


'--------------------------------------------------
' Hands-On 19-10
'--------------------------------------------------

Sub ListSoftware()
    With InfoSurvey.lboxSystems
        .AddItem "Spreadsheets"
        .AddItem "Databases"
        .AddItem "CAD Systems"
        .AddItem "Word Processing"
        .AddItem "Finance Programs"
        .AddItem "Games"
        .AddItem "Accounting Programs"
        .AddItem "Desktop Publishing"
        .AddItem "Imaging Software"
        .AddItem "Personal Information Managers"
    End With
End Sub


'--------------------------------------------------
' Hands-On 19-11
'--------------------------------------------------

Private Sub optSoft_Change()
    Me.lboxSystems.Clear
    Call ListSoftware
    Me.lboxSystems.ListIndex = 0
    Me.picImage.Picture = LoadPicture("C:\Ex07_HandsOn\books.bmp")
End Sub


Private Sub optHard_Change()
    Me.lboxSystems.Clear
    Call ListHardware
    Me.lboxSystems.ListIndex = 0
    Me.picImage.Picture = LoadPicture("C:\Ex07_HandsOn\cd.bmp")
End Sub


'--------------------------------------------------
' Hands-On 19-12
'--------------------------------------------------

Private Sub spPercent_Change()
    txtPercent.Value = spPercent.Value
End Sub


Private Sub txtPercent_Change()
    Dim entry As String
       
    On Error Resume Next
   
    entry = Me.txtPercent.Value
        If entry > 100 Then
            entry = 0
            Me.txtPercent.Value = entry
        End If
    spPercent.Value = txtPercent.Value
End Sub


'--------------------------------------------------
' Hands-On 19-13
'--------------------------------------------------

Private Sub cmdCancel_Click()
    Me.Hide
End Sub


'--------------------------------------------------
' Hands-On 19-14
'--------------------------------------------------

Private Sub cmdOK_Click()
    Dim r As Integer
    Me.Hide
    
    r = Application.CountA(Range("A:A"))
    Range("A1").Offset(r + 1, 0) = Me.lboxSystems.Value
    
    If Me.optHard.Value = True Then
        Range("A1").Offset(r + 1, 1) = "*"
    End If
    If Me.optSoft.Value = True Then
        Range("A1").Offset(r + 1, 2) = "*"
    End If
    If Me.chkIBM.Value = True Then
        Range("A1").Offset(r + 1, 3) = "*"
    End If
    If Me.chkNote.Value = True Then
        Range("A1").Offset(r + 1, 4) = "*"
    End If
    If Me.chkMac.Value = True Then
        Range("A1").Offset(r + 1, 5) = "*"
    End If
    Range("A1").Offset(r + 1, 6) = Me.cboxWhereUsed.Value
    Range("A1").Offset(r + 1, 7) = Me.txtPercent.Value
    
    If Me.optMale.Value = True Then
        Range("A1").Offset(r + 1, 8) = "*"
    End If
    If Me.optFemale.Value = True Then
        Range("A1").Offset(r + 1, 9) = "*"
    End If
   Unload Me
End Sub


'--------------------------------------------------
' Hands-On 19-15
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-16
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------


'--------------------------------------------------
' Hands-On 19-17
'--------------------------------------------------

Sub DoStudents()
    Students.Show
End Sub


'--------------------------------------------------
' Hands-On 19-18
'--------------------------------------------------

' Global Declarations
Dim r As Integer
Dim nr As Integer
Dim indexPlus As Integer
Dim YesNo As Integer
Dim startRow As Integer
Dim endRow As Integer

Private Sub UserForm_Initialize()
    Me.MultiPage1.Value = 0
    optDataEntry.Value = True
    optViewData.Value = False
    If ActiveSheet.UsedRange.Rows.Count > 2 Then
        optViewData.Enabled = True
    Else
        optViewData.Enabled = False
        lboxStudents.Visible = False
    End If
    lblNames.Visible = False
         
    With Me.cboxYear
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
    End With
    
    With Me.cboxMajor
        .AddItem "English"
        .AddItem "Chemistry"
        .AddItem "Mathematics"
        .AddItem "Linguistics"
        .AddItem "Computer Science"
    End With
    
    With Me.cboxGrade
        .AddItem "A"
        .AddItem "B"
        .AddItem "C"
        .AddItem "D"
        .AddItem "F"
    End With
    
    Me.Calendar1.Value = Date
    
    Me.lblDate.Caption = Me.Calendar1.Value
    Me.TabStrip1.Value = 0
    Me.txtSSN.SetFocus
End Sub


Private Sub optDataEntry_Click()
    cmdAddStudent.Visible = True
    lblNames.Visible = False
    lboxStudents.Visible = False
        
    Me.MultiPage1(1).Enabled = False
        If lboxStudents.RowSource <> "" Then
            Me.txtSSN.Text = ""
            Me.txtLast.Text = ""
            Me.txtFirst.Text = ""
            Me.cboxYear.Text = ""
            Me.cboxMajor.Text = ""
            Me.txtSSN.SetFocus
        End If
    Me.txtSSN.SetFocus
End Sub


Private Sub optViewData_Click()
    startRow = 3
    endRow = ActiveSheet.UsedRange.Rows.Count
    If endRow > 2 Then
        lblNames.Visible = True
        lboxStudents.Visible = True
        lboxStudents.RowSource = _
          Range(Cells(startRow, 2), Cells(endRow, 3)).Address
        lboxStudents.ListIndex = 0
        cmdAddStudent.Visible = False
   End If
End Sub


Private Sub lboxStudents_Change()
    indexPlus = lboxStudents.ListIndex + 3
 
    With ActiveWorkbook.Worksheets("Students and Exams")
        Me.txtSSN.Text = Range("A" & indexPlus).Value
        Me.txtLast.Text = Range("B" & indexPlus).Value
        Me.txtFirst.Text = Range("C" & indexPlus).Value
        Me.cboxYear.Text = Range("D" & indexPlus).Value
        Me.cboxMajor.Text = Range("E" & indexPlus).Value
    
     Call TabStrip1_Change
     Me.MultiPage1(1).Enabled = True
    End With
End Sub


Private Sub cmdAddStudent_Click()
    If IsFilled = False Then
        MsgBox "Please enter all the data " & _
            "or click Close to exit."
        Me.txtSSN.SetFocus
        Exit Sub
    End If
    If Me.optDataEntry.Value = True Then
        Me.Hide
        ActiveWorkbook.Sheets("Students and Exams").Select
        r = ActiveSheet.UsedRange.Rows.Count
        nr = r + 1
        Range("A" & nr).Value = Me.txtSSN.Text
        Range("B" & nr).Value = Me.txtLast.Text
        Range("C" & nr).Value = Me.txtFirst.Text
        Range("D" & nr).Value = Me.cboxYear.Text
        Range("E" & nr).Value = Me.cboxMajor.Text
        
        Me.txtSSN.Text = ""
        Me.txtLast.Text = ""
        Me.txtFirst.Text = ""
        Me.cboxYear.Text = ""
        Me.cboxMajor.Text = ""
        Me.txtSSN.SetFocus
        
        Me.optViewData.Enabled = True
        ' redisplay the form
        Me.Show
    End If
End Sub


Private Sub cmdClose_Click()
    Unload Me
    Set Students = Nothing
End Sub


Private Sub cboxGrade_Click()
    YesNo = MsgBox("Enter the grade in the worksheet?", _
        vbYesNo, "Modify Grade")
    If YesNo = 6 Then
       Me.lblGrade.Caption = cboxGrade.Value
       Select Case TabStrip1.Value
        Case 0
          Range("F" & indexPlus).Value = Me.lblGrade.Caption
        Case 1
            Range("H" & indexPlus).Value = Me.lblGrade.Caption
        Case 2
            Range("J" & indexPlus).Value = Me.lblGrade.Caption
        Case 3
            Range("L" & indexPlus).Value = Me.lblGrade.Caption
        End Select
        cboxGrade.Value = ""
    End If
End Sub


Private Sub Calendar1_Click()
    YesNo = MsgBox("Enter the date in the worksheet?", _
        vbYesNo, "Modify Date")
    If YesNo = 6 Then
       Me.lblDate.Caption = Calendar1.Value
       Select Case TabStrip1.Value
        Case 0
            Range("G" & indexPlus).Value = Me.lblDate.Caption
        Case 1
            Range("I" & indexPlus).Value = Me.lblDate.Caption
        Case 2
            Range("K" & indexPlus).Value = Me.lblDate.Caption
        Case 3
            Range("M" & indexPlus).Value = Me.lblDate.Caption
        End Select
    End If
End Sub


Private Sub TabStrip1_Change()
    indexPlus = lboxStudents.ListIndex + 3

    With ActiveWorkbook.Worksheets("Students and Exams")
        Select Case TabStrip1.Value
            Case 0  ' English
                Me.lblGrade.Caption = Range("F" & indexPlus).Value
                Me.lblDate.Caption = Range("G" & indexPlus).Value
            Case 1 'French
                Me.lblGrade.Caption = Range("H" & indexPlus).Value
                Me.lblDate.Caption = Range("I" & indexPlus).Value
            Case 2  'Math
                Me.lblGrade.Caption = Range("J" & indexPlus).Value
                Me.lblDate.Caption = Range("K" & indexPlus).Value
            Case 3 'Physics
                Me.lblGrade.Caption = Range("L" & indexPlus).Value
                Me.lblDate.Caption = Range("M" & indexPlus).Value
        End Select
    End With
End Sub


Private Sub MultiPage1_Change()
    Me.lblWho.Caption = Me.txtLast.Value & ", " _
        & Me.txtFirst.Value
    Call TabStrip1_Change
End Sub


'--------------------------------------------------
' Hands-On 19-19
' No code in this Hands-On.
' Please follow the instructions in the book.
'--------------------------------------------------





